package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Guest;



public class DbGuest {
	private Connection con;
	
	public DbGuest() {
		con = DbConnection.getInstance().getDBcon();
	}
	
public ArrayList<Guest> getAll(boolean retriveAssociation) {
		
		return miscWhere("", retriveAssociation);
		
	}

private ArrayList<Guest> miscWhere(String wClause, boolean retrieveAssociation)
{
        ResultSet results;
    ArrayList<Guest> list = new ArrayList<Guest>();	
	
    String query =  buildQuery(wClause);

        try{ // read the employee from the database
	Statement stmt = con.createStatement();
 	stmt.setQueryTimeout(5);
 	System.out.println(query);
 	results = stmt.executeQuery(query);
 	

	while( results.next() ){
     	 Guest oObj = new Guest();
	 oObj = buildGuest(results);	
             list.add(oObj);	
	}//end while
             stmt.close();     
        }
 	catch(Exception e){
 		System.out.println("Query exception - select: "+e);
		e.printStackTrace();
 	}
	return list;
}

private String buildQuery(String wClause)
{
    String query="SELECT id, fname, lname, address, country, phone, email, travelAgency  FROM Guest";
	
	if (wClause.length()>0)
		query=query+" WHERE "+ wClause;
		
	return query;
}

private Guest buildGuest(ResultSet results)
{   Guest oObj = new Guest();
    try{ // the columns from the table emplayee  are used
    	oObj.setId(results.getInt("id"));
    	oObj.setFname(results.getString("fname"));
    	oObj.setLname(results.getString("lname"));
    	oObj.setAddress(results.getString("address"));
    	oObj.setCountry(results.getString("country"));
    	oObj.setPhone(results.getString("phone"));
    	oObj.setEmail(results.getString("email"));
    	oObj.setTravelAgency(results.getString("travelAgency"));
    	
    	
          
    }
   catch(Exception e)
   {
       System.err.println("error in building the product object");
   }
   return oObj;
}


public int insert(Guest guest) throws Exception {
	 //call to get the next ssn number
    

   int rc = -1;
   String query="INSERT INTO Guest(fname, lname, address, country, phone, email, travelAgency)  VALUES('"+
  		guest.getFname()  + "','"  +
	    guest.getLname()  + "','"  +
  		guest.getAddress() + "','" +
	    guest.getCountry() + "','" +
  		guest.getPhone() + "','" +
	    guest.getEmail() + "','" +
  		guest.getTravelAgency() + "')";
        
        
                 

   System.out.println("insert : " + query);
  try{ // insert new employee +  dependent
      Statement stmt = con.createStatement();
      stmt.setQueryTimeout(5);
 	  rc = stmt.executeUpdate(query);
      stmt.close();
  }//end try
   catch(SQLException ex){
      System.out.println("Order ikke oprettet");
      System.out.println("SQLException says: " + ex.getMessage());
     throw new Exception ("Order is not inserted correct");
   }
   return(rc);
}


//Singelwhere is used when we only select one employee 	
	private Guest singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Guest empObj = new Guest();
              
	        String query =  buildQuery(wClause);
              System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                          empObj = buildGuest(results);
                          //assocaition is to be build
                          stmt.close();
                         // if(retrieveAssociation)
                         // {   //The supervisor and department is to be build as well
                         //     int guestId = empObj.getId();
                          //  list = singleWhere(" ssn = '" + superssn + "'",false);
                         //     empObj.setSupervisor(superEmp);
                          //    System.out.println("Supervisor is seleceted");
                              //here the department has to be selected as well
                         
                         
                         // }
			}
                      else{ //no employee was found
                          empObj = null;
                      }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}

	public Guest findGuest(int guestId, boolean b) {
		String query = "id = " + guestId;
		return singleWhere(query, b);
	}
	

}
